// SalesDAO.java
package com.ecommerce.dao;

import com.ecommerce.model.CategorySales;
import com.ecommerce.model.ProductSales;
import com.ecommerce.model.SalesReport;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SalesDAO {

    public SalesReport generateSalesReport(java.util.Date startDate, java.util.Date endDate) {
        SalesReport report = new SalesReport();
        report.setStartDate(startDate);
        report.setEndDate(endDate);

        String revenueSql = "SELECT SUM(total_amount) as total_revenue, COUNT(*) as total_orders " +
                "FROM orders WHERE created_at BETWEEN ? AND ? AND status != 'cancelled'";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(revenueSql)) {

            // 修复：在DAO层进行日期转换
            stmt.setDate(1, new java.sql.Date(startDate.getTime()));
            stmt.setDate(2, new java.sql.Date(endDate.getTime()));
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                report.setTotalRevenue(rs.getDouble("total_revenue"));
                report.setTotalOrders(rs.getInt("total_orders"));
            }

            // 获取商品销售排行
            report.setProductSales(getProductSales(startDate, endDate));

            // 获取分类销售统计
            report.setCategorySales(getCategorySales(startDate, endDate));

            // 计算总商品销量
            int totalProducts = 0;
            for (ProductSales ps : report.getProductSales()) {
                totalProducts += ps.getQuantitySold();
            }
            report.setTotalProductsSold(totalProducts);

        } catch (SQLException e) {
            System.out.println("生成销售报告失败: " + e.getMessage());
        }

        return report;
    }

    private List<ProductSales> getProductSales(java.util.Date startDate, java.util.Date endDate) {
        List<ProductSales> productSales = new ArrayList<>();
        String sql = "SELECT p.id, p.name, SUM(oi.quantity) as total_quantity, " +
                "SUM(oi.subtotal) as total_revenue " +
                "FROM order_items oi " +
                "JOIN orders o ON oi.order_id = o.id " +
                "JOIN products p ON oi.product_id = p.id " +
                "WHERE o.created_at BETWEEN ? AND ? AND o.status != 'cancelled' " +
                "GROUP BY p.id, p.name " +
                "ORDER BY total_revenue DESC";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setDate(1, new java.sql.Date(startDate.getTime()));
            stmt.setDate(2, new java.sql.Date(endDate.getTime()));
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                ProductSales ps = new ProductSales();
                ps.setProductId(rs.getInt("id"));
                ps.setProductName(rs.getString("name"));
                ps.setQuantitySold(rs.getInt("total_quantity"));
                ps.setTotalRevenue(rs.getDouble("total_revenue"));
                productSales.add(ps);
            }

        } catch (SQLException e) {
            System.out.println("获取商品销售数据失败: " + e.getMessage());
        }

        return productSales;
    }

    private List<CategorySales> getCategorySales(java.util.Date startDate, java.util.Date endDate) {
        List<CategorySales> categorySales = new ArrayList<>();
        String sql = "SELECT c.id, c.name, SUM(oi.quantity) as total_quantity, " +
                "SUM(oi.subtotal) as total_revenue " +
                "FROM order_items oi " +
                "JOIN orders o ON oi.order_id = o.id " +
                "JOIN products p ON oi.product_id = p.id " +
                "JOIN categories c ON p.category_id = c.id " +
                "WHERE o.created_at BETWEEN ? AND ? AND o.status != 'cancelled' " +
                "GROUP BY c.id, c.name " +
                "ORDER BY total_revenue DESC";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setDate(1, new java.sql.Date(startDate.getTime()));
            stmt.setDate(2, new java.sql.Date(endDate.getTime()));
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                CategorySales cs = new CategorySales();
                cs.setCategoryId(rs.getInt("id"));
                cs.setCategoryName(rs.getString("name"));
                cs.setQuantitySold(rs.getInt("total_quantity"));
                cs.setTotalRevenue(rs.getDouble("total_revenue"));
                categorySales.add(cs);
            }

        } catch (SQLException e) {
            System.out.println("获取分类销售数据失败: " + e.getMessage());
        }

        return categorySales;
    }

    public double getDailyRevenue() {
        String sql = "SELECT SUM(total_amount) as revenue FROM orders " +
                "WHERE DATE(created_at) = CURDATE() AND status != 'cancelled'";

        try (Connection conn = DatabaseConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            if (rs.next()) {
                return rs.getDouble("revenue");
            }

        } catch (SQLException e) {
            System.out.println("获取今日营收失败: " + e.getMessage());
        }

        return 0;
    }

    public int getDailyOrders() {
        String sql = "SELECT COUNT(*) as order_count FROM orders " +
                "WHERE DATE(created_at) = CURDATE() AND status != 'cancelled'";

        try (Connection conn = DatabaseConnection.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            if (rs.next()) {
                return rs.getInt("order_count");
            }

        } catch (SQLException e) {
            System.out.println("获取今日订单数失败: " + e.getMessage());
        }

        return 0;
    }
}